﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using DelonixRegoliaHotelManagementSystem;

namespace DelonixRegoliaHotelManagementSystem
{
    class StaffDBManager
    {
        public static ArrayList GetAll()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegoliaHotelManagementSystem.Properties.Settings.DelonixRegoliaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff m = new Staff();
                    m.No = (int)dr["No"];
                    m.Name = (string)dr["Name"];
                    m.Username = (string)dr["Username"];
                    m.Password = (string)dr["Password"];
                    m.Job = (string)dr["Job"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Staff GetStaffByUsername(string username)
        {
            Staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegoliaHotelManagementSystem.Properties.Settings.DelonixRegoliaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE Username=@Username";
                comm.Parameters.AddWithValue("@Username", username);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Staff();
                    m.No = (int)dr["No"];
                    m.Name = (string)dr["Name"];
                    m.Username = (string)dr["Username"];
                    m.Password = (string)dr["Password"];
                    m.Job = (string)dr["Job"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }


        public static int InsertStaff(Staff m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegoliaHotelManagementSystem.Properties.Settings.DelonixRegoliaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staff(Name,Username,Password,Job)" +
                    " VALUES (@Name,@Username,@Password,@Job)";
                comm.Parameters.AddWithValue("@Username", m.Username);
                comm.Parameters.AddWithValue("@Password", m.Password);
                comm.Parameters.AddWithValue("@Name", m.Name);
                comm.Parameters.AddWithValue("@Job", m.Job);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateStaff(Staff m)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegoliaHotelManagementSystem.Properties.Settings.DelonixRegoliaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET Name=@Name,Username=@Username,Password=@Password, Job=@Job where No=@No";
                comm.Parameters.AddWithValue("@Username", m.Username);
                comm.Parameters.AddWithValue("@Password", m.Password);
                comm.Parameters.AddWithValue("@Name", m.Name);
                comm.Parameters.AddWithValue("@No", m.No);
                comm.Parameters.AddWithValue("@Job", m.Job);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStaff(string name)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegoliaHotelManagementSystem.Properties.Settings.DelonixRegoliaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from staff where Name=@Name";
                comm.Parameters.AddWithValue("@Name", name);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
    }
}
